package cn.east196.tfx.db;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import cn.east196.tfx.util.NameUtil;

@Service
public class DatabaseService {

	private static final Logger LOGGER = LoggerFactory.getLogger(DatabaseService.class);
	
	@Autowired
	JdbcTemplate jdbcTemplate;

	public List<String> schemas() {
		List<String> schemas = jdbcTemplate.queryForList("SELECT SCHEMA_NAME FROM information_schema.SCHEMATA", String.class);
		LOGGER.info("list schemas: {}", schemas);
		return schemas;
	}
	

	public List<String> tables(String schema) {
		LOGGER.info("list tables: {}");
		List<String> tables = jdbcTemplate.queryForList("select TABLE_NAME from information_schema.TABLES WHERE TABLE_SCHEMA=?", String.class,schema);
		LOGGER.info("list tables: {}", tables);
		return tables;
	}
	

	public List<Map<String, Object>> fields(String schema,String table) {
		String query = String.format("show full fields from %s.%s", schema,table);
		List<Map<String, Object>> fields = jdbcTemplate.queryForList(query);
		LOGGER.info("list fields: {}", fields);
		return fields;
	}
	

	public List<TableField> fs(String schema,String table) {
		return fields(schema,table).stream().map(rs->{
			try {
				return warpperd(rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return null;
		}).collect(Collectors.toList());
	}
	
	  public TableField warpperd(Map<String, Object> rs) throws SQLException {
		    String key, extra, fieldText, typeText, nullable, comment;
		    fieldText = rs.get("field").toString();
		    typeText = rs.get("type").toString();
		    nullable = rs.get("null").toString();
		    key = rs.get("key").toString();
		    extra = rs.get("extra").toString();
		    comment = rs.get("comment").toString();
		    TableField tableField = new TableField();
		    tableField.setFieldText(fieldText);
		    tableField.setFieldName(NameUtil.asFieldName(fieldText));
		    tableField.setTypeText(typeText);
		    tableField.setComment(comment);
		    tableField.setFunName(NameUtil.asClassName(fieldText));
		    if (nullable.equals("YES")) {
		      tableField.setNullable("true");
		    } else {
		      tableField.setNullable("false");
		    }
		    switch (key) {
		      case "PRI":
		        tableField.setKey("Id");
		        break;
		      default:
		        tableField.setKey("Basic");
		    }
		    switch (extra) {
		      case "auto_increment":
		        tableField.setExtra("auto_increment");
		        break;
		    }
		    if (typeText.contains("char") | typeText.contains("text")) {
		      tableField.setType("String");
		    } else if (typeText.contains("int")) {
		      tableField.setType("Integer");
		    } else if (typeText.contains("date") | typeText.contains("time")) {
		      tableField.setType("Long");
		    } else if (typeText.contains("blob")) {
		      tableField.setType("Byte[]");
		    } else if (typeText.contains("double")) {
		      tableField.setType("Double");
		    } else if (typeText.contains("float")) {
		      tableField.setType("Float");
		    } else {
		      tableField.setType("Object");
		    }
		    return tableField;
		  }
}
